/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package br.com.ufpb.halloc.dados;

import br.com.ufpb.halloc.conexaoDB.Pool;
import br.com.ufpb.halloc.conexaoDB.PoolIF;
import br.com.ufpb.halloc.negocio.Centro;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Filipe
 */
public class CentroDAOPostgres implements CentroDAO {

    private static CentroDAOPostgres daoPostgres;  
    private PoolIF pool;
    
    public static CentroDAOPostgres getInstance(){
        if (daoPostgres == null)
            daoPostgres = new CentroDAOPostgres();
        return daoPostgres;
    }
    
    /* COSTRUTOR */
    private CentroDAOPostgres(){  
        this.pool = new Pool();
    }
    
    
    
    @Override
    public void salvar(Centro cen) {
        if (existe(cen.getCodigo())) {
        } else {

            Connection con = this.pool.getConnection();
            PreparedStatement ps;

            String sqlInsert = "INSERT INTO centro (nome, endereco, codcentro)  VALUES (?, ?, ?);";

            try {
                ps = con.prepareStatement(sqlInsert);
                ps.setString(1, cen.getNome());
                ps.setString(2, cen.getEndereco());
                ps.setString(3, cen.getCodigo());
                ps.execute();
                ps.close();

            } catch (SQLException ex) {
                Logger.getLogger(ProfessorDAOPostgres.class.getName()).log(Level.SEVERE, null, ex);
            } finally {
                pool.liberarConnection(con);
            }
        }
    }

    @Override
    public void remover(Centro cen) {
        Connection con = this.pool.getConnection();
        PreparedStatement ps;

        String sqlExcluir = "DELETE FROM centro WHERE codcentro = ?;";
				
	try {
            ps = con.prepareStatement(sqlExcluir);
            ps.setString(1, cen.getCodigo());
	    ps.executeUpdate();
	    ps.close();
            
         } catch (SQLException ex) {
            Logger.getLogger(ProfessorDAOPostgres.class.getName()).log(Level.SEVERE, null, ex);
         } finally {
            pool.liberarConnection(con);
	 }
    }

    @Override
    public void removerPorCodigo(String codigo) {
         Connection con = this.pool.getConnection();
        PreparedStatement ps;

        String sqlExcluir = "DELETE FROM centro WHERE codcentro = ?;";
				
	try {
            ps = con.prepareStatement(sqlExcluir);
            ps.setString(1, codigo);
	    ps.executeUpdate();
	    ps.close();
            
         } catch (SQLException ex) {
            Logger.getLogger(ProfessorDAOPostgres.class.getName()).log(Level.SEVERE, null, ex);
         } finally {
            pool.liberarConnection(con);
	 }
    }

    @Override
    public void atualizar(Centro p) {
        Connection con = pool.getConnection();
       PreparedStatement ps;
       String sqlAtualizar = "UPDATE centro SET nome = ?, endereco = ? WHERE codcentro = ?;";	
        try {          
           ps = con.prepareStatement(sqlAtualizar);	    
           ps.setString(1, p.getNome());
           ps.setString(2, p.getEndereco());
           ps.setString(3, p.getCodigo());
           ps.executeUpdate();
           ps.close();
        } catch (SQLException ex) {
                Logger.getLogger(ProfessorDAOPostgres.class.getName()).log(Level.SEVERE, null, ex);
	} finally {
            pool.liberarConnection(con);
	}
    }

    @Override
    public Centro getCentro(Centro c) {
        Connection con = pool.getConnection();
        PreparedStatement ps;
        ResultSet rs;

        String sqlSelect = "SELECT * FROM centro WHERE codCentro = ?;";
        Centro cen = new Centro();
        try {
            ps = con.prepareStatement(sqlSelect);
            ps.setString(1, c.getCodigo());
            rs = ps.executeQuery();
            cen.setCodigo(rs.getString("codcentro"));
            cen.setNome(rs.getString("nome"));
            cen.setEndereco(rs.getString("endereco"));
            ps.close();
        } catch (SQLException ex) {
            Logger.getLogger(ProfessorDAOPostgres.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            pool.liberarConnection(con);
        }
        return cen;
    }

    @Override
    public Centro getCentroPorCodigo(String codigo) {
       Connection con = pool.getConnection();
        PreparedStatement ps;
        ResultSet rs;

        String sqlSelect = "SELECT * FROM centro WHERE codCentro = ?;";
        Centro cen = new Centro();
        try {
            ps = con.prepareStatement(sqlSelect);
            ps.setString(1, codigo);
            rs = ps.executeQuery();
            cen.setCodigo(rs.getString("codcentro"));
            cen.setNome(rs.getString("nome"));
            cen.setEndereco(rs.getString("endereco"));
            ps.close();
        } catch (SQLException ex) {
            Logger.getLogger(ProfessorDAOPostgres.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            pool.liberarConnection(con);
        }
        return cen;
    }

    @Override
    public List<Centro> getCentros() {
        Connection con = pool.getConnection();
        PreparedStatement ps;
        ResultSet rs;

        String sqlSelect = "SELECT * FROM centro;";
        Centro cen;
        List<Centro> retorno = new ArrayList<Centro>();
        try {
            ps = con.prepareStatement(sqlSelect);
            rs = ps.executeQuery();
            while(rs.next()){
                cen = new Centro();
                cen.setCodigo(rs.getString("codcentro"));
                cen.setNome(rs.getString("nome"));
                cen.setEndereco(rs.getString("endereco"));
                retorno.add(cen);
            }   
            ps.close();
        } catch (SQLException ex) {
            Logger.getLogger(ProfessorDAOPostgres.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            pool.liberarConnection(con);
        }
        return retorno;
    }

    @Override
    public int size() {
        Connection con = pool.getConnection();
        PreparedStatement ps;
        ResultSet rs;
        String sqlSelect = "SELECT COUNT(*) as total FROM centro;";
        int total = 0;
        try {
            ps = con.prepareStatement(sqlSelect);
            rs = ps.executeQuery();
            total = rs.getInt("total");
                
               
            ps.close();
        } catch (SQLException ex) {
            Logger.getLogger(ProfessorDAOPostgres.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            pool.liberarConnection(con);
        }
        return total;
    }
    
    
    public boolean existe(String codigoCentro){
        Connection con = pool.getConnection();
        PreparedStatement ps;
        ResultSet rs;

        String sqlSelect = "SELECT * FROM centro WHERE codcentro = ?;";
        try {
            ps = con.prepareStatement(sqlSelect);
            ps.setString(1, codigoCentro);
            rs = ps.executeQuery();
            if (rs.getString("codcentro").equals(null)){
                return false;
            }
            ps.close();
        } catch (SQLException ex) {
            Logger.getLogger(ProfessorDAOPostgres.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            pool.liberarConnection(con);
        }
        
        return true;
    }
    
}
